package com.dtt.company.employee.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.dtt.company.department.model.DepartmentModel;
import com.dtt.company.employee.model.EmployeeModel;
import com.dtt.company.util.JDBCUtil;

public class EmployeeDao {
	
	private String table="employee";
	private String col="code,psd,name,sex,entime,dep_code,email,head";
//	private String col="code,psd,name,dep_code";
	private Connection conn=null;
	private PreparedStatement ps=null;
	
	
	public Integer insert(EmployeeModel model) {
		StringBuffer sql=new StringBuffer("insert into ");
		sql.append(table).append(" (").append(col).append(')').append(" value(?,?,?,?,?,?,?,?)");//append(" value(?,?,?,?)");
		System.out.println(sql.toString());
		conn=JDBCUtil.getConnection();
		try {
			ps=conn.prepareStatement(sql.toString());
			ps.setString(1, model.getCode());
			ps.setString(2, model.getPsd());
			ps.setString(3, model.getName());
			ps.setString(4, model.getSex());
			ps.setString(5, model.getEntime());
			ps.setString(6, model.getDepCode());
//			ps.setString(4, model.getDepCode());
			ps.setString(7, model.getEmail());
			ps.setString(8, model.getHead());
			return ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.close(conn, ps);
		}
		return null;
	}
	
	public Integer delete(EmployeeModel model) {
		StringBuffer sql=new StringBuffer("delete from ");
		sql.append(table);
		List<Object> vals=appendWhere(sql,model);
		conn=JDBCUtil.getConnection();
		try {
			ps=conn.prepareStatement(sql.toString());
			for(int i=0;i<vals.size();i++) 
				ps.setObject(i+1, vals.get(i));				
			return ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.close(conn, ps);
		}
		return null;
	}
	public Integer update(EmployeeModel model) {
		StringBuffer sql=new StringBuffer("update ");
		sql.append(table);
		List<Object> vals=appendUpdate(sql,model);
		System.out.println(sql);
		conn=JDBCUtil.getConnection();
		try {
			ps=conn.prepareStatement(sql.toString());
			for(int i=0;i<vals.size();i++) 
				ps.setObject(i+1, vals.get(i));				
			return ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.close(conn, ps);
		}
		return null;
	}
	
	public List<EmployeeModel> select(EmployeeModel model){
		StringBuffer sql=new StringBuffer("select ").append(col);
		sql.append(",(select d.name from department d where d.code=dep_code) dep_name");
		sql.append(" from ").append(table);//code,psd,name,sex,entime,depCode,email,head
		List<Object> vals=appendWhere(sql,model);
		List<EmployeeModel> list=new ArrayList<>();
		conn=JDBCUtil.getConnection();
		ResultSet rs=null;
		System.out.println(sql);
		try {
			ps=conn.prepareStatement(sql.toString());
			for(int i=0;i<vals.size();i++) {
				ps.setObject(i+1, vals.get(i));	
//				System.out.println(vals.get(i));
			}				
//			System.out.println(sql.toString());
			rs= ps.executeQuery();
			while(rs.next()) {
				EmployeeModel md=new EmployeeModel();
				md.setCode(rs.getString("code"));
				md.setName(rs.getString("name"));
				md.setPsd(rs.getString("psd"));
				md.setSex(rs.getString("sex"));
				md.setEntime(rs.getString("entime"));
				md.setDepCode(rs.getString("dep_code"));
				md.setEmail(rs.getString("email"));
				md.setHead(rs.getString("head"));
				md.setDeptName(rs.getString("dep_name"));
				list.add(md);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.close(conn, ps,rs);
		}
		return list;
		
	}
	
	public int selectCount(EmployeeModel model){
		StringBuffer sql=new StringBuffer("select count(id) from ");
		sql.append(table);
		List<Object> vals=appendWhere(sql,model);
		List<EmployeeModel> list=new ArrayList<>();
		conn=JDBCUtil.getConnection();
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql.toString());
			for(int i=0;i<vals.size();i++) {
				ps.setObject(i+1, vals.get(i));	
//				System.out.println(vals.get(i));
			}				
//			System.out.println(sql.toString());
			rs= ps.executeQuery();
			if(rs.next())
				return rs.getInt(1);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.close(conn, ps,rs);
		}
		return 0;
		
	}
	
	public List<Object> appendWhere(StringBuffer sql, EmployeeModel model) {
		sql.append(" where 1=1");
		List<Object> objs=new ArrayList<Object>();
		//entime,depCode,email
		String code=model.getCode();
		if(code!=null&&!code.trim().isEmpty()) {//!"".equals(code.trim())
			sql.append(" and code like ?");
			objs.add(code);
		}		
		String name=model.getName();
		if(name!=null&&!name.trim().isEmpty()) {
			sql.append(" and name like ?");
			objs.add(name);
		}
		String sex=model.getSex();
		if(sex!=null&&!sex.trim().isEmpty()) {
			sql.append(" and sex like ?");
			objs.add(sex);
		}
		String depCode=model.getDepCode();
		if(depCode!=null&&!depCode.trim().isEmpty()) {
			sql.append(" and dep_code like ?");
			objs.add(depCode);
		}
		String email=model.getEmail();
		if(email!=null&&!email.trim().isEmpty()) {
			sql.append(" and email like ?");
			objs.add(email);
		}
		String order=model.getOrderby();
		if(order!=null&&!order.trim().isEmpty()) {
			sql.append(" order by ").append(order);
			//不能用？，因为？上的数据带引号
		}	
		if(model.isPageOn()) {
			sql.append(" limit ?,? ");
			objs.add(model.getRowStart());
			objs.add(model.getPageLimit());//objs.add(model.getRowCount());
		}
		return objs;
	}
	public List<Object> appendUpdate(StringBuffer sql,EmployeeModel model ){
		List<Object> objs=new ArrayList<Object>();//code,psd,name,sex,entime,depCode,email,head
		sql.append(" set id=id");		
		String name=model.getName();
		if(name!=null) {
			sql.append(",name = ?");
			objs.add(name);
		}
		String psd=model.getPsd();
		if(psd!=null) {
			sql.append(",psd = ?");
			objs.add(psd);
		}
		String sex=model.getSex();
		if(sex!=null) {
			sql.append(",sex = ?");
			objs.add(sex);
		}
		String entime=model.getEntime();
		if(entime!=null) {
			sql.append(",entime = ?");
			objs.add(entime);
		}
		String depCode=model.getDepCode();
		if(depCode!=null) {
			sql.append(",dep_code = ?");
			objs.add(depCode);
		}
		String email=model.getEmail();
		if(email!=null) {
			sql.append(",email = ?");
			objs.add(email);
		}
		String head=model.getHead();
		if(head!=null) {
			sql.append(",head = ?");
			objs.add(head);
		}
		String code=model.getCode();
		sql.append(" where code=?");
		objs.add(code);
		return objs;
	}
	
	public static void main(String[] args) {
		EmployeeModel model=new EmployeeModel();
		model.setCode("yg2103");
//		model.setName("张三san");
//		model.setPsd("123");
//		model.setDepCode("%dt%");
//		model.setSex("男");
//		Date d=new Date();		
//		model.setEntime(d.toLocaleString());;
		EmployeeDao dao=new EmployeeDao();
//		Integer r=dao.insert(model);
//		Integer r=dao.update(model);
		List<EmployeeModel> list=dao.select(model);
//		Integer r=dao.delete(model);
//		System.out.println(r);
		if(list!=null) {
			for(EmployeeModel li:list) {
				System.out.println(li.toString());
			}
		}
//		else System.out.println("空");
		
	}

}
